﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using com.IronOne.BoardPACWinAppBO.General;
using com.IronOne.BoardPACWinAppBO.Sync;
using com.IronOne.BoardPACWinAppDAO.DbOperations;
using com.IronOne.BoardPACWinAppDAO.DB;
using com.IronOne.BoardPACWinAppUtil.Enums;
using com.IronOne.BoardPACWinAppUtil.Util;
using GalaSoft.MvvmLight.Messaging;
using SQLite;
using com.IronOne.BoardPACWinAppBO.Paper;

namespace com.IronOne.BoardPACWinAppDAO.Sync
{
    public class SyncDAO : ISyncDAO
    {
        private readonly IDbOperations _dbOperations;

        public SyncDAO()
        {
            _dbOperations = new DbOperations.DbOperations();
        }

        public async Task<SyncDataModel> GetSyncHttpPostParameters()
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var sdm = new SyncDataModel();
                var user = await conn.Table<User>().Where(x => x.UserId == Global.UserId).FirstOrDefaultAsync();
                if (user != null)
                {
                    sdm.CustomUserId = user.UserId;
                    sdm.LastProfileImageSyncDateTicks = user.LastProfileImageSyncDateTicks;
                    //sdm.IsFirstTime = true; //_user.IsFirstTime;

                    sdm.AccessesTimeStamp = user.AccessesTimeStamp;
                    sdm.CategoryTimeStamp = user.CategoryTimeStamp;
                    sdm.CommentTimeStamp = user.CommentsTimeStamp;
                    sdm.HeadingTimeStamp = user.HeadingTimeStamp;
                    sdm.MeetingTimeStamp = user.MeetingsTimeStamp;
                    sdm.MemberTimeStamp = user.MembersTimeStamp;
                    sdm.NewsItemTimeStamp = user.NewsItemTimeStamp;
                    sdm.PaperTimeStamp = user.PaperTimeStamp;
                    sdm.SecurityTimeStamp = user.SecurityTimeStamp;
                    sdm.SharedDocTimeStamp = user.SharedDocTimeStamp;
                    sdm.SubCategoryTimeStamp = user.SubCategoryTimeStamp;

                    //Sync 2 Parameters
                    sdm.SettingTimeStamp = user.SettingTimeStamp;
                    //sdm.AnnotBackupTimeStamp = user.AnnotBackupTimeStamp;
                }

                try
                {
                    sdm.CatergoryLastId =
                        (await conn.Table<MainCategory>().OrderByDescending(x => x.CategoryID).FirstOrDefaultAsync())
                            .CategoryID;
                }
                catch (Exception)
                {
                } //Comment PendingStatus != 1 means comment not synced with the server yet
                try
                {
                    sdm.CommentLastId =
                        (await
                            conn.Table<DB.Comments>()
                                .Where(y => y.PendingStatus != 1)
                                .OrderByDescending(x => x.CommentId)
                                .FirstOrDefaultAsync()).CommentId;
                }
                catch (Exception)
                {
                }
                try
                {
                    sdm.HeadingLastId =
                        (await conn.Table<Heading>().OrderByDescending(x => x.HeadingID).FirstOrDefaultAsync())
                            .HeadingID;
                }
                catch (Exception)
                {
                }
                try
                {
                    sdm.MeetingLastId =
                        (await conn.Table<DB.Meeting>().OrderByDescending(x => x.MeetingId).FirstOrDefaultAsync())
                            .MeetingId;
                }
                catch (Exception)
                {
                }
                try
                {
                    sdm.MemberLastId =
                        (await conn.Table<Member>().OrderByDescending(x => x.MemberId).FirstOrDefaultAsync()).MemberId;
                }
                catch (Exception)
                {
                }
                try
                {
                    sdm.NewsItemLastId =
                        (await
                            conn.Table<Paper>()
                                .Where(s => s.DocType == (byte) PaperTypes.NewsItem)
                                .OrderByDescending(x => x.PaperId)
                                .FirstOrDefaultAsync()).PaperId;
                }
                catch (Exception)
                {
                }
                try
                {
                    sdm.PaperLastId =
                        (await conn.Table<Paper>().OrderByDescending(x => x.PaperId).FirstOrDefaultAsync()).PaperId;
                }
                catch (Exception)
                {
                }
                try
                {
                    sdm.SharedDocLastId =
                        (await
                            conn.Table<Paper>()
                                .Where(s => s.DocType == (byte) PaperTypes.SharedDoc)
                                .OrderByDescending(x => x.PaperId)
                                .FirstOrDefaultAsync()).PaperId;
                }
                catch (Exception)
                {
                }
                try
                {
                    sdm.SubCatergoryLastId =
                        (await conn.Table<SubCategory>().OrderByDescending(x => x.SubCategoryId).FirstOrDefaultAsync())
                            .SubCategoryId;
                }
                catch (Exception)
                {
                }

                //Sync 2 Parameters
                try
                {
                    sdm.AnnotationShareId =
                        (await
                            conn.Table<AnnotationShareWithUser>()
                                .OrderByDescending(x => x.AnnotationShareId)
                                .FirstOrDefaultAsync()).AnnotationShareId;
                }
                catch (Exception)
                {
                }
                try
                {
                    sdm.SettingLastId =
                        (await conn.Table<Settings>().OrderByDescending(x => x.SettingID).FirstOrDefaultAsync())
                            .SettingID;
                }
                catch (Exception)
                {
                }

                return sdm;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<SyncDataModel> GetAnnotRestoreHttpPostParameters()
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var sdm = new SyncDataModel();
                var user = await conn.Table<User>().Where(x => x.UserId == Global.UserId).FirstOrDefaultAsync();
                if (user != null)
                {
                    sdm.ShareAnnotTimeStamp = user.ShareAnnotTimeStamp;
                }

                try
                {
                    sdm.LastAnnotationShareId =
                         (await
                            conn.Table<AnnotationRestore>()
                                .OrderByDescending(x => x.BackupID)
                                .FirstOrDefaultAsync()).BackupID;
                }
                catch (Exception)
                {
                }

                return sdm;
            }
            catch(Exception)
            {
                throw;
            }
        }

        public async Task<ResponseDataModel> SaveSyncDownloadDataToLocalDb(DownloadDataModel ddm)
        {
            var rdm = new ResponseDataModel();
            var sudm = new SyncUpdatedDataModel();

            //This variable will updated as true if sql or gen exception encounter on the while loop below
            //and it will update the returning rdm as well, which prevents sync success message going to transmit (which determines as sync success)
            var isSyncFailed = false;

            try
            {
                var connAsync = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                await connAsync.RunInTransactionAsync((connection) =>
                {
                    connection = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);

                    var meetingIds = new List<int>();
                    List<DB.Meeting> updatedMeetings = null;
                    var comments = new List<KeyValuePair<string, string>>();
                    var headingIds = new List<string>();
                    var agendaItemIds = new List<string>();

                    string[] delimiter1 = {"values ("};
                    string[] delimiter2 = {"meetingid"};

                    foreach (var listItems in ddm.QueryData)
                    {
                        #region foreach

                        try
                        {
                            foreach (var item in listItems)
                            {
                                var itemLow = item.ToLower();

                                if (itemLow.StartsWith("insert into meeting"))
                                {
                                    #region insert into meeting

                                    var tmp0 = itemLow.Split(delimiter1, StringSplitOptions.None)[1];
                                    tmp0 = tmp0.Split(',')[0].Trim();
                                    meetingIds.Add(Convert.ToInt32(tmp0));

                                    connection.Execute(item);
                                    continue;

                                    #endregion
                                }
                                if (itemLow.StartsWith("update meeting"))
                                {
                                    #region update meeting

                                    var tmp1 = itemLow.Split(delimiter2, StringSplitOptions.None)[1];
                                    tmp1 = tmp1.Split('=')[1].Trim();
                                    meetingIds.Add(Convert.ToInt32(tmp1));

                                    connection.Execute(item);
                                    continue;

                                    #endregion
                                }
                                if (itemLow.StartsWith("insert into subcategory") ||
                                    itemLow.StartsWith("update subcategory") ||
                                    itemLow.StartsWith("delete from subcategory"))
                                {
                                    if (sudm.IsNewOrUpdatedSubCategories)
                                        connection.Execute(item);
                                    else
                                        sudm.IsNewOrUpdatedSubCategories = connection.Execute(item) > 0;

                                    continue;
                                }
                                if (itemLow.StartsWith("insert into maincategory") ||
                                    itemLow.StartsWith("update maincategory") ||
                                    itemLow.StartsWith("delete from maincategory"))
                                {
                                    if (sudm.IsNewOrUpdatedCategories)
                                        connection.Execute(item);
                                    else
                                        sudm.IsNewOrUpdatedCategories = connection.Execute(item) > 0;

                                    continue;
                                }
                                if (itemLow.StartsWith("insert into comments"))
                                {
                                    #region insert into comments

                                    var tmp0 = itemLow.Split(delimiter1, StringSplitOptions.None)[1];
                                    var commType = tmp0.Split(',')[8].Trim();
                                    comments.Add(commType.Equals("0")
                                        ? new KeyValuePair<string, string>("0", tmp0.Split(',')[3].Trim())
                                        : new KeyValuePair<string, string>("1", tmp0.Split(',')[3].Trim()));
                                    // Meeting Id

                                    connection.Execute(item);
                                    continue;

                                    #endregion
                                }

                                if (itemLow.StartsWith("insert into heading"))
                                {
                                    #region insert into heading

                                    var tmp0 = itemLow.Split(delimiter1, StringSplitOptions.None)[1]; //MeetingId
                                    var meetingId = tmp0.Split(',')[4].Trim();
                                    headingIds.Add(meetingId);

                                    connection.Execute(item);
                                    continue;

                                    #endregion
                                }
                                if (itemLow.StartsWith("update heading") || itemLow.StartsWith("delete from heading"))
                                {
                                    if (sudm.IsUpdatedOrDeletedHeadingItems)
                                        connection.Execute(item);
                                    else
                                        sudm.IsUpdatedOrDeletedHeadingItems = connection.Execute(item) > 0;

                                    continue;
                                }

                                if (itemLow.StartsWith("insert into paper"))
                                {
                                    if (itemLow.Contains("refid"))
                                    {
                                        #region insert into paper

                                        var tmp0 = itemLow.Split(delimiter1, StringSplitOptions.None)[1];
                                            //refid == MeetingId
                                        var refid = tmp0.Split(',')[2].Trim();
                                        agendaItemIds.Add(refid);

                                        #endregion
                                    }
                                    else //For shared docs and news
                                    {
                                        if (sudm.IsNewOrUpdatedNewsOrSharedItems)
                                            connection.Execute(item);
                                        else
                                            sudm.IsNewOrUpdatedNewsOrSharedItems = connection.Execute(item) > 0;

                                        continue;
                                    }

                                    connection.Execute(item);
                                    continue;
                                }
                                if (itemLow.StartsWith("update paper") || itemLow.StartsWith("delete from paper"))
                                {
                                    if (sudm.IsUpdatedOrDeletedAgendaItems)
                                        connection.Execute(item);
                                    else
                                        sudm.IsUpdatedOrDeletedAgendaItems = connection.Execute(item) > 0;

                                    continue;
                                }

                                connection.Execute(item);
                            }
                        }
                        catch (SQLiteException)
                        {
                            connection.Rollback();
                            isSyncFailed = true;
                        }
                        catch (Exception)
                        {
                            isSyncFailed = true;
                        }

                        #endregion
                    }

                    #region Parallel.ForEach

                    /*Parallel.ForEach(ddm.QueryData, listItems =>
                    {
                        #region Parallel.ForEach
                        try
                        {
                            foreach (string item in listItems)
                            {
                                string itemLow = item.ToLower();

                                if (itemLow.StartsWith("insert into meeting"))
                                {
                                    #region insert into meeting
                                    string tmp0 = itemLow.Split(delimiter1, StringSplitOptions.None)[1];
                                    tmp0 = tmp0.Split(',')[0].Trim();
                                    meetingIds.Add(Convert.ToInt32(tmp0));

                                    connection.Execute(item);
                                    return;
                                    #endregion
                                }
                                if (itemLow.StartsWith("update meeting"))
                                {
                                    #region update meeting
                                    string tmp1 = itemLow.Split(delimiter2, StringSplitOptions.None)[1];
                                    tmp1 = tmp1.Split('=')[1].Trim();
                                    meetingIds.Add(Convert.ToInt32(tmp1));

                                    connection.Execute(item);
                                    return;
                                    #endregion
                                }
                                if (itemLow.StartsWith("insert into subcategory") || itemLow.StartsWith("update subcategory") || itemLow.StartsWith("delete from subcategory"))
                                {
                                    if (sudm.IsNewOrUpdatedSubCategories)
                                        connection.Execute(item);
                                    else
                                        sudm.IsNewOrUpdatedSubCategories = connection.Execute(item) > 0 ? true : false;

                                    continue;
                                }
                                if (itemLow.StartsWith("insert into maincategory") || itemLow.StartsWith("update maincategory") || itemLow.StartsWith("delete from maincategory"))
                                {
                                    if (sudm.IsNewOrUpdatedCategories)
                                        connection.Execute(item);
                                    else
                                        sudm.IsNewOrUpdatedCategories = connection.Execute(item) > 0 ? true : false;

                                    return;
                                }
                                if (itemLow.StartsWith("insert into comments"))
                                {
                                    #region insert into comments
                                    string tmp0 = itemLow.Split(delimiter1, StringSplitOptions.None)[1];
                                    string commType = tmp0.Split(',')[8].Trim();
                                    if (commType.Equals("0"))   // Paper Comment
                                        comments.Add(new KeyValuePair<string, string>("0", tmp0.Split(',')[3].Trim())); // Meeting Id
                                    else                        // Meeting Comment
                                        comments.Add(new KeyValuePair<string, string>("1", tmp0.Split(',')[3].Trim())); // Meeting Id

                                    connection.Execute(item);
                                    return;
                                    #endregion
                                }

                                if (itemLow.StartsWith("insert into heading"))
                                {
                                    #region insert into heading
                                    string tmp0 = itemLow.Split(delimiter1, StringSplitOptions.None)[1]; //MeetingId
                                    string meetingId = tmp0.Split(',')[4].Trim();
                                    headingIds.Add(meetingId);

                                    connection.Execute(item);
                                    return;
                                    #endregion
                                }
                                if (itemLow.StartsWith("update heading") || itemLow.StartsWith("delete from heading"))
                                {
                                    if (sudm.IsUpdatedOrDeletedHeadingItems)
                                        connection.Execute(item);
                                    else
                                        sudm.IsUpdatedOrDeletedHeadingItems = connection.Execute(item) > 0 ? true : false;

                                    return;
                                }

                                if (itemLow.StartsWith("insert into paper"))
                                {
                                    if (itemLow.Contains("refid"))
                                    {
                                        #region insert into paper
                                        string tmp0 = itemLow.Split(delimiter1, StringSplitOptions.None)[1]; //refid == MeetingId
                                        string refid = tmp0.Split(',')[2].Trim();
                                        agendaItemIds.Add(refid);
                                        #endregion
                                    }
                                    else  //For shared docs and news
                                    {
                                        if (sudm.IsNewOrUpdatedNewsOrSharedItems)
                                            connection.Execute(item);
                                        else
                                            sudm.IsNewOrUpdatedNewsOrSharedItems = connection.Execute(item) > 0 ? true : false;

                                        return;
                                    }

                                    connection.Execute(item);
                                    continue;
                                }
                                if (itemLow.StartsWith("update paper") || itemLow.StartsWith("delete from paper"))
                                {
                                    if (sudm.IsUpdatedOrDeletedAgendaItems)
                                        connection.Execute(item);
                                    else
                                        sudm.IsUpdatedOrDeletedAgendaItems = connection.Execute(item) > 0 ? true : false;

                                    return;
                                }

                                connection.Execute(item);
                            }
                        }
                        catch (SQLiteException)
                        {
                            connection.Rollback();
                            isSyncFailed = true;
                            return;
                        }
                        catch (Exception)
                        {
                            isSyncFailed = true;
                            return;
                        }
                        #endregion
                    });*/

                    #endregion

                    #region Map models to send

                    if (meetingIds != null && meetingIds.Count > 0)
                        updatedMeetings =
                            connection.Table<DB.Meeting>().Where(m => meetingIds.Contains(m.MeetingId)).ToList();
                    if (updatedMeetings != null && updatedMeetings.Count > 0)
                        sudm.Meetings = EntityToModelMapper.MapMeetingEntityToMeetingModel(updatedMeetings);

                    if (comments != null && comments.Count > 0)
                        sudm.Comments = comments;

                    if (headingIds != null && headingIds.Count > 0)
                        sudm.HeadingIds = headingIds;

                    if (agendaItemIds != null && agendaItemIds.Count > 0)
                        sudm.AgendaItemIds = agendaItemIds;

                    #endregion

                    #region Send message

                    if ((sudm.Meetings == null || sudm.Meetings.Count <= 0) && !sudm.IsNewOrUpdatedCategories &&
                        !sudm.IsNewOrUpdatedSubCategories && (sudm.Comments == null || sudm.Comments.Count <= 0) &&
                        (sudm.HeadingIds == null || sudm.HeadingIds.Count <= 0) &&
                        (sudm.AgendaItemIds == null || sudm.AgendaItemIds.Count <= 0) &&
                        !sudm.IsUpdatedOrDeletedAgendaItems && !sudm.IsNewOrUpdatedNewsOrSharedItems) return;

                    var mdm = new MessageDataModel {MessageData = sudm};
                    var message = new NotificationMessage<MessageDataModel>(mdm, MessageCodes.SYNC_UPDATED_DATA);
                    Messenger.Default.Send(message);

                    #endregion
                });

                if (isSyncFailed)
                {
                    rdm.IsError = true;
                    rdm.ErrorMessage = "Sync data did't save successfully to the database";
                }
            }
            catch (Exception e)
            {
                rdm.IsError = true;
                rdm.ErrorMessage = e.Message;
            }
            return rdm;
        }

        public async Task<List<LogDataModel>> GetLogRecords()
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var logs = await conn.Table<Log>().OrderByDescending(x => x.LogId).ToListAsync();
                var ldms = EntityToModelMapper.MapMemberToMemberModel(logs);

                return ldms;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> DeleteLogRecord(int logId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var log = await conn.Table<Log>().Where(x => x.LogId == logId).FirstOrDefaultAsync();
                if (log != null)
                {
                    var delRecStatus = await conn.DeleteAsync(log);
                }
            }
            catch (Exception)
            {
                throw;
            }
            return true;
        }

        public async Task MarkPaperAsDownloaded(int paperId, int versionId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var paper =
                    await
                        conn.Table<Paper>()
                            .Where(p => p.PaperId == paperId && p.VersionId == versionId)
                            .FirstOrDefaultAsync();
                if (paper != null)
                {
                    paper.DownloadStatus = ApplicationConstants.PAPERDOWNLOADEDSTATUS;
                    await conn.UpdateAsync(paper);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task MarkAnnotationFileAsDownloaded(int paperId, int versionId, int annotationShareId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var aswu =
                    await
                        conn.Table<AnnotationShareWithUser>()
                            .Where(
                                p =>
                                    p.PaperId == paperId && p.VersionId == versionId &&
                                    p.AnnotationShareId == annotationShareId)
                            .FirstOrDefaultAsync();
                if (aswu != null)
                {
                    aswu.IsFileDownloaded = ApplicationConstants.PAPERDOWNLOADEDSTATUS;
                    await conn.UpdateAsync(aswu);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetPaperName(int paperId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var paperEntity = await conn.Table<Paper>().Where(p => p.PaperId == paperId).FirstOrDefaultAsync();
                return paperEntity.DocName;
            }
            catch (Exception)
            {
                throw;
            }
        }





        public async Task<List<PaperModel>> GetAnnotRestoreData()
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var restoreData = await conn.Table<AnnotationRestore>().Where(x => x.DownloadStatus == (int)PaperDownloadStatuses.NotDownloded).ToListAsync();

                List<PaperModel> models = new List<PaperModel>();
                foreach (var item in restoreData)
                {
                    models.Add(new PaperModel {  RefId = item.BackupID, PaperId = item.PaperID, VersionId = item.VersionID });
                }

                return models;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}